package DB;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Gui.Terminal;
import Model.Invoice;

/**
 * @author Anders
 * IKKE TESTET
 */
public class DBInvoice 
{
	//If anything changes in the database, then this should be easy to find and edit:
	private final String invoiceNo = "InvoiceNo";
	private final String paymentDate = "paymentDate";
	private final String amount = "amount";
	private final String selectAll = String.format("SELECT %s, %s, %s FROM Invoice", invoiceNo, paymentDate, amount);
	
	private DBConnection connection;
	
	public DBInvoice()
	{
		connection = DBConnection.getInstance();
	}
	
	public Invoice getInvoice(String invNo)
	{
		Invoice result = null;
		//create statement
		Statement stmt = null;
		try {
			stmt = connection.getDBcon().createStatement();
		} catch (SQLException e1) {
			
			Terminal.say("An error occured during statement creation...");
			Terminal.say("Stack trace: ");
			Terminal.say(e1.getStackTrace().toString());
		}
		
		//excecute statment
		String statement = selectWhere(invoiceNo + " = " + invNo);
		try {
			stmt.execute(statement);
		} catch (SQLException e) {
			Terminal.say("An error occured executing a statment: ");
			Terminal.say(statement);
			Terminal.say();
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		//get result
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("The resultSet could not be retrieved \n Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		if(resSet != null) {
			try {
				if(resSet.next()) {
					result = buildInvoice(resSet);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				Terminal.say("An errro occured trying to build Invoice", e);
			}
		}
		else {
			Terminal.say("No result was found");
		}
		
		//close connection
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				Terminal.say("Stack trace: ");
				Terminal.say(e.getStackTrace().toString());
			}
		}
		
		return result;
	}
	
	public ArrayList<Invoice> getAllInvoices()
	{
		ArrayList<Invoice> allInvoices = new ArrayList<Invoice>();
		
		//create statement
		Statement stmt = null;
		try {
			stmt = connection.getDBcon().createStatement();
		} catch (SQLException e) {
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		//execute statemnt
		try {
			stmt.execute(selectAll);
		} catch (SQLException e) {
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		//retrieve results
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		//build an Invoice object for each result
		try {
			
			while(resSet.next()) {
				allInvoices.add(buildInvoice(resSet));
			}
			
		} catch (SQLException e) {
			
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		//close connection
		if(stmt != null) {
			try {
				stmt.close();
			} catch (Exception e) {
				Terminal.say("Stack trace: ");
				Terminal.say(e.getStackTrace().toString());
			}
		}
		
		return allInvoices;
	}
	
	public boolean addInvoice(Invoice invoice)
	{
		//we assume all is going well
		boolean success = true;
		
		//create statement
		Statement stmt = null;
		
		try {
			stmt = connection.getDBcon().createStatement();
		} catch (SQLException e) {
			Terminal.say("Could not create statement to add invoice to DB", e);
			success = false;
		}
		 
		//execute statement
		String invNo = invoice.getInvoiceNo();
		String paymentDate = invoice.getPaymentDate();
		String amount = "" + invoice.getAmount();
		String statement = (String.format("INSERT INTO Invoice VALUES ('%s', '%s', '%s')", invNo, paymentDate, amount));
		try {
			
			stmt.execute(statement);
		}
		catch(Exception e) {
			Terminal.say(String.format("An error occured with statement \n %s", statement), e);
			success = false;
		}
		
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Could not close statement", e);
		}
		
		
		return success;
	}
	
	public boolean deleteInvoice(String invNo)
	{
		boolean success = true;
		
		//create statement
		Statement stmt = null;
		
		try {
			stmt = connection.getDBcon().createStatement();
		} catch (SQLException e) {
			Terminal.say("Could not create statement to delete invoice to DB", e);
			success = false;
		}
		
		try {
			stmt.execute(String.format("DELETE FROM Invoice WHERE %s = %s", invoiceNo, invNo));
		} catch (SQLException e) {
			Terminal.say(String.format("Could not delete %s from database", invNo), e);
			success = false;
		}
		
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Could not close statement", e);
		}
		
		
		return success;
	}
	
	public String getHighestNo()
	{
		Statement stmt = null;
		
		try {
			stmt = connection.getDBcon().createStatement();
		} catch (SQLException e) {
			Terminal.say("Could not create statement to delete invoice to DB", e);
		}
		
		try {
			stmt.execute(String.format("SELECT MAX(CAST(%s AS Int)) AS Res FROM Invoice", this.invoiceNo));
		} catch (SQLException e) {
			Terminal.say("Could not find highest invoice no", e);
		}
		
		ResultSet resSet = null;
		
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("Could not get ResultSet from getHighestNo", e);
		}
		
		String result = null;
		try {
			resSet.next();
			int hiNo = resSet.getInt("Res");
			result = hiNo + "";
		}
		catch(Exception e) {
			Terminal.say("En Error occured in getHighestNo()", e);
		}
		
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Could not close statement", e);
		}
		
		return result;
	}
	
	//********************************************** PRIVATE METHODS *********************************************************
	
	private String selectWhere(String condition)
	{
		return selectAll + " WHERE " + condition;
	}
	
	private Invoice buildInvoice(ResultSet resSet) 
	{
		try {
			
			String invoiceNo = resSet.getString(this.invoiceNo);
			String paymentDate = resSet.getString(this.paymentDate);
			double amount = resSet.getDouble(this.amount);
			
			return new Invoice(invoiceNo, paymentDate, amount);
		}
		catch(SQLException e) {
			Terminal.say("Could not build Invoice object!", e);
			
			return null;
		}
	}

}
